-----------------------------------------------------------------------------------------------------------------------------------------------
/****** Object:  Table [dbo].[USER]    Script Date: 02/05/2012 12:24:28 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USER]') AND type in (N'U'))
DROP TABLE [dbo].[USER]
GO


/****** Object:  Table [dbo].[USER]    Script Date: 02/05/2012 12:24:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[USER](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[USERNAME] [nvarchar](200) NOT NULL,
	[PASSWORD] [nvarchar](200) NULL,
	[FIRST_NAME] [nvarchar](200) NOT NULL,
	[MIDDLE_NAME] [nvarchar](200) NULL,
	[LAST_NAME] [nvarchar](200) NOT NULL,
	[EMAIL] [nvarchar](100) NULL,
	[LASTCHANGE_DATE] [datetime] NULL,
	[ACTIVE] [bit] NOT NULL,
 CONSTRAINT [PK_USER] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-----------------------------------------------------------------------------------------------------------------------------------------------
/****** Object:  Table [dbo].[USER_PERMISSION]    Script Date: 02/05/2012 12:23:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USER_PERMISSION]') AND type in (N'U'))
DROP TABLE [dbo].[USER_PERMISSION]
GO

/****** Object:  Table [dbo].[USER_PERMISSION]    Script Date: 02/05/2012 12:23:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[USER_PERMISSION](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[PERMISSION_NAME] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_USER_PERMISSION] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
-----------------------------------------------------------------------------------------------------------------------------------------------
/****** Object:  Table [dbo].[USER_ROLE]    Script Date: 02/05/2012 12:25:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USER_ROLE]') AND type in (N'U'))
DROP TABLE [dbo].[USER_ROLE]
GO

/****** Object:  Table [dbo].[USER_ROLE]    Script Date: 02/05/2012 12:25:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[USER_ROLE](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ROLE_NAME] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_USER_ROLE] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
-----------------------------------------------------------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_USER_ASSIGN_ROLE_USER]') AND parent_object_id = OBJECT_ID(N'[dbo].[USER_ASSIGN_ROLE]'))
ALTER TABLE [dbo].[USER_ASSIGN_ROLE] DROP CONSTRAINT [FK_USER_ASSIGN_ROLE_USER]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_USER_ASSIGN_ROLE_USER_ROLE]') AND parent_object_id = OBJECT_ID(N'[dbo].[USER_ASSIGN_ROLE]'))
ALTER TABLE [dbo].[USER_ASSIGN_ROLE] DROP CONSTRAINT [FK_USER_ASSIGN_ROLE_USER_ROLE]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_USER_ASSIGN_ROLE_ACTIVE]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[USER_ASSIGN_ROLE] DROP CONSTRAINT [DF_USER_ASSIGN_ROLE_ACTIVE]
END

GO

/****** Object:  Table [dbo].[USER_ASSIGN_ROLE]    Script Date: 02/05/2012 12:26:07 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USER_ASSIGN_ROLE]') AND type in (N'U'))
DROP TABLE [dbo].[USER_ASSIGN_ROLE]
GO

/****** Object:  Table [dbo].[USER_ASSIGN_ROLE]    Script Date: 02/05/2012 12:26:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[USER_ASSIGN_ROLE](
	[USERID] [int] NOT NULL,
	[USER_ROLE_ID] [int] NOT NULL,
	[START_DATE] [datetime] NULL,
	[END_DATE] [datetime] NULL,
	[ACTIVE] [bit] NOT NULL,
 CONSTRAINT [PK_USER_ASSIGN_ROLE] PRIMARY KEY CLUSTERED 
(
	[USERID] ASC,
	[USER_ROLE_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[USER_ASSIGN_ROLE]  WITH CHECK ADD  CONSTRAINT [FK_USER_ASSIGN_ROLE_USER] FOREIGN KEY([USERID])
REFERENCES [dbo].[USER] ([ID])
GO

ALTER TABLE [dbo].[USER_ASSIGN_ROLE] CHECK CONSTRAINT [FK_USER_ASSIGN_ROLE_USER]
GO

ALTER TABLE [dbo].[USER_ASSIGN_ROLE]  WITH CHECK ADD  CONSTRAINT [FK_USER_ASSIGN_ROLE_USER_ROLE] FOREIGN KEY([USER_ROLE_ID])
REFERENCES [dbo].[USER_ROLE] ([ID])
GO

ALTER TABLE [dbo].[USER_ASSIGN_ROLE] CHECK CONSTRAINT [FK_USER_ASSIGN_ROLE_USER_ROLE]
GO

ALTER TABLE [dbo].[USER_ASSIGN_ROLE] ADD  CONSTRAINT [DF_USER_ASSIGN_ROLE_ACTIVE]  DEFAULT ((1)) FOR [ACTIVE]
GO
-----------------------------------------------------------------------------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ROLE_ASSING_PERMISSION_USER_PERMISSION]') AND parent_object_id = OBJECT_ID(N'[dbo].[ROLE_ASSING_PERMISSION]'))
ALTER TABLE [dbo].[ROLE_ASSING_PERMISSION] DROP CONSTRAINT [FK_ROLE_ASSING_PERMISSION_USER_PERMISSION]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ROLE_ASSING_PERMISSION_USER_ROLE]') AND parent_object_id = OBJECT_ID(N'[dbo].[ROLE_ASSING_PERMISSION]'))
ALTER TABLE [dbo].[ROLE_ASSING_PERMISSION] DROP CONSTRAINT [FK_ROLE_ASSING_PERMISSION_USER_ROLE]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_ROLE_ASSING_PERMISSION_ACTIVE]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[ROLE_ASSING_PERMISSION] DROP CONSTRAINT [DF_ROLE_ASSING_PERMISSION_ACTIVE]
END

GO

/****** Object:  Table [dbo].[ROLE_ASSING_PERMISSION]    Script Date: 02/05/2012 12:34:10 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ROLE_ASSING_PERMISSION]') AND type in (N'U'))
DROP TABLE [dbo].[ROLE_ASSING_PERMISSION]
GO

/****** Object:  Table [dbo].[ROLE_ASSING_PERMISSION]    Script Date: 02/05/2012 12:34:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ROLE_ASSING_PERMISSION](
	[USER_ROLE_ID] [int] NOT NULL,
	[USER_PERMISSION_ID] [int] NOT NULL,
	[PERMISSION_START_DATE] [datetime] NOT NULL,
	[PERMISSION_END_DATE] [datetime] NOT NULL,
	[ACTIVE] [bit] NOT NULL,
 CONSTRAINT [PK_ROLE_ASSING_PERMISSION] PRIMARY KEY CLUSTERED 
(
	[USER_ROLE_ID] ASC,
	[USER_PERMISSION_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ROLE_ASSING_PERMISSION]  WITH CHECK ADD  CONSTRAINT [FK_ROLE_ASSING_PERMISSION_USER_PERMISSION] FOREIGN KEY([USER_PERMISSION_ID])
REFERENCES [dbo].[USER_PERMISSION] ([ID])
GO

ALTER TABLE [dbo].[ROLE_ASSING_PERMISSION] CHECK CONSTRAINT [FK_ROLE_ASSING_PERMISSION_USER_PERMISSION]
GO

ALTER TABLE [dbo].[ROLE_ASSING_PERMISSION]  WITH CHECK ADD  CONSTRAINT [FK_ROLE_ASSING_PERMISSION_USER_ROLE] FOREIGN KEY([USER_ROLE_ID])
REFERENCES [dbo].[USER_ROLE] ([ID])
GO

ALTER TABLE [dbo].[ROLE_ASSING_PERMISSION] CHECK CONSTRAINT [FK_ROLE_ASSING_PERMISSION_USER_ROLE]
GO

ALTER TABLE [dbo].[ROLE_ASSING_PERMISSION] ADD  CONSTRAINT [DF_ROLE_ASSING_PERMISSION_ACTIVE]  DEFAULT ((1)) FOR [ACTIVE]
GO



-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------